--
-- Description: View for displaying the rolled up data by operating system for events.
-- Date created: 12 May 2008
--

set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go

--
-- drop the view if it exists and recreate it.
--
if exists
(
	select * 
	from sys.views
	where object_id = OBJECT_ID(N'[dbo].[EventHitsOperatingSystemRollup_vw]')
)
begin
	drop view [dbo].[EventHitsOperatingSystemRollup_vw]
end
go

create view [dbo].[EventHitsOperatingSystemRollup_vw] 
as
	select
	EH.[EventID]
	, ET.[EventTypeName]
	, EH.[OperatingSystemID]
	, OS.[OperatingSystemName]
	, OS.[OperatingSystemVersion]
	, SUM(EH.[TotalHits]) TotalHits
	from [dbo].[EventHits] EH (nolock)
	inner join [dbo].[EventTypes] ET (nolock)
		on EH.EventTypeID = ET.EventTypeID
	inner join [dbo].[OperatingSystems] OS (nolock)
		on EH.OperatingSystemID = OS.OperatingSystemID
	group by EH.EventID
			, ET.EventTypeName
			, EH.OperatingSystemID
			, OS.OperatingSystemName
			, OS.OperatingSystemVersion
go
